package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPayeeAcctDao;
import com.boss.salary.entity.PayeeAcct;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * <br>Title:
 * <br>Description:
 * <br>Author:
 * <br>Date:
 */
@Repository
public class PayeeAcctDaoImpl extends BaseDaoImpl implements IPayeeAcctDao {


    @Override
    public List<PayeeAcct> getAccountInfo(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT id, mof_div_code, year, create_time, update_time, payee_acct_name, ")
                .append(" payee_acct_no, payee_acct_code, payee_acct_bank_name, is_deleted,show_name ");
        sql.append(" FROM ").append(TableName.PAYEE_ACCT);
        sql.append(" WHERE  is_deleted = ?");
        paramList.add(SystemDictConstant.NOT_DELETED);
        sql.append(" and mof_div_code = ? ");
        paramList.add(queryParam.get("mofDivCode"));
//        sql.append(" and `year` = ? ");
//        paramList.add(queryParam.get("year"));
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctName"))) {
            String payeeAcctName = queryParam.get("payeeAcctName").toString();
            sql.append(" and payee_acct_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctName);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctNo"))) {
            String payeeAcctNo = queryParam.get("payeeAcctNo").toString();
            sql.append(" and payee_acct_no like concat( '%', ?, '%')");
            paramList.add(payeeAcctNo);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctCode"))) {
            String payeeAcctCode = queryParam.get("payeeAcctCode").toString();
            sql.append(" and payee_acct_code like concat( '%', ?, '%')");
            paramList.add(payeeAcctCode);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctBankName"))) {
            String payeeAcctBankName = queryParam.get("payeeAcctBankName").toString();
            sql.append(" and payee_acct_bank_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctBankName);
        }
        sql.append(" ORDER BY payee_acct_code,year");
//        Object[] objects = new Object[]{
//                queryParam.get("mofDivCode"), queryParam.get("year"), SystemDictConstant.IS_FALSE
//    };
        String querySql = boxUpSql(sql.toString());
        Object[] params = boxUpParam(paramList.toArray(), paginationDTO);
        return this.getjdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(PayeeAcct.class), params);
    }
    @Override
    public int getAccountInfoTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(*) ");
        sql.append(" FROM ").append(TableName.PAYEE_ACCT);
        sql.append(" WHERE  is_deleted = ?");
        paramList.add(SystemDictConstant.NOT_DELETED);
        sql.append(" and mof_div_code = ? ");
        paramList.add(queryParam.get("mofDivCode"));
//        sql.append(" and `year` = ? ");
//        paramList.add(queryParam.get("year"));
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctName"))) {
            String payeeAcctName = queryParam.get("payeeAcctName").toString();
            sql.append(" and payee_acct_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctName);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctNo"))) {
            String payeeAcctNo = queryParam.get("payeeAcctNo").toString();
            sql.append(" and payee_acct_no like concat( '%', ?, '%')");
            paramList.add(payeeAcctNo);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctCode"))) {
            String payeeAcctCode = queryParam.get("payeeAcctCode").toString();
            sql.append(" and payee_acct_code like concat( '%', ?, '%')");
            paramList.add(payeeAcctCode);
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctBankName"))) {
            String payeeAcctBankName = queryParam.get("payeeAcctBankName").toString();
            sql.append(" and payee_acct_bank_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctBankName);
        }
        return this.getjdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public int checkRepeat(PayeeAcct payeeAcct) {
        List<Object> params = new ArrayList<>(3);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(*) ");
        sql.append(" FROM ").append(TableName.PAYEE_ACCT);
        sql.append(" WHERE mof_div_code = ? and is_deleted = ?");
        params.add(payeeAcct.getMofDivCode());
        params.add(SystemDictConstant.IS_FALSE);
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctNo())) {
            sql.append(" and payee_acct_no = ? ");
            params.add(payeeAcct.getPayeeAcctNo());
        }
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctCode())) {
            sql.append(" and payee_acct_code = ? ");
            params.add(payeeAcct.getPayeeAcctCode());
        }
        if (!StringUtils.isEmpty(payeeAcct.getId())) {
            sql.append(" and id != ? ");
            params.add(payeeAcct.getId());
        }
        return this.getjdbcTemplate().queryForObject(sql.toString(), params.toArray(), Integer.class);
    }

    @Override
    public void saveAccount(PayeeAcct payeeAcct) {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO ").append(TableName.PAYEE_ACCT);
        sql.append(" ( id, mof_div_code, year, create_time, update_time, payee_acct_name, payee_acct_code, payee_acct_no, ")
                .append(" payee_acct_bank_name, is_deleted,show_name)");
        sql.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        Object[] objects = new Object[]{
                payeeAcct.getId(), payeeAcct.getMofDivCode(), payeeAcct.getYear(), payeeAcct.getCreateTime(),
                payeeAcct.getUpdateTime(), payeeAcct.getPayeeAcctName(), payeeAcct.getPayeeAcctCode(), payeeAcct.getPayeeAcctNo(),
                payeeAcct.getPayeeAcctBankName(), payeeAcct.getIsDeleted(),payeeAcct.getShowName()
        };
        this.getjdbcTemplate().update(sql.toString(), objects);

    }

    @Override
    public void updateAccount(PayeeAcct payeeAcct) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE ").append(TableName.PAYEE_ACCT).append(" SET ")
                .append(" update_time = ?");
        paramList.add(payeeAcct.getUpdateTime());
        if (!StringUtils.isEmpty(payeeAcct.getMofDivCode())) {
            sql.append(" , mof_div_code = ? ");
            paramList.add(payeeAcct.getMofDivCode());
        }
        if (!StringUtils.isEmpty(payeeAcct.getYear())) {
            sql.append(" , year = ? ");
            paramList.add(payeeAcct.getYear());
        }
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctName())) {
            sql.append(" , payee_acct_name = ? ");
            paramList.add(payeeAcct.getPayeeAcctName());
        }
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctNo())) {
            sql.append(" , payee_acct_no = ? ");
            paramList.add(payeeAcct.getPayeeAcctNo());
        }
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctNo())) {
            sql.append(" , payee_acct_code = ? ");
            paramList.add(payeeAcct.getPayeeAcctCode());
        }
        if (!StringUtils.isEmpty(payeeAcct.getPayeeAcctBankName())) {
            sql.append(" , payee_acct_bank_name = ? ");
            paramList.add(payeeAcct.getPayeeAcctBankName());
        }
        if (!StringUtils.isEmpty(payeeAcct.getIsDeleted())) {
            sql.append(" , is_deleted = ? ");
            paramList.add(payeeAcct.getIsDeleted());
        }
        if (!StringUtils.isEmpty(payeeAcct.getShowName())) {
            sql.append(" , show_name = ? ");
            paramList.add(payeeAcct.getShowName());
        }
        sql.append(" WHERE id = ? ");
        paramList.add(payeeAcct.getId());
        this.getjdbcTemplate().update(sql.toString(), paramList.toArray());
    }

    @Override
    public List<PayeeAcct> selectBankCodeAndNameByProvince(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select distinct ").append(" payee_acct_code, payee_acct_bank_name ")
                .append(" from ").append(TableName.PAYEE_ACCT)
                .append(" where ").append(" mof_div_code = ? ");
        return super.getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(PayeeAcct.class), province);
    }

    @Override
    public List<PayeeAcct> getAccountInfoList(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT id, mof_div_code, year, create_time, update_time, payee_acct_name, ")
                .append(" payee_acct_no, payee_acct_code, payee_acct_bank_name, is_deleted,show_name ");
        sql.append(" FROM ").append(TableName.PAYEE_ACCT);
        sql.append(" WHERE  is_deleted ='0' and mof_div_code=?");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(PayeeAcct.class), province);

    }

    @Override
    public void insertPayeeAcctList(List<PayeeAcct> payeeAcctsList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO ").append(TableName.PAYEE_ACCT);
        sql.append(" ( id, mof_div_code, year, create_time, update_time, payee_acct_name, payee_acct_code, payee_acct_no, ")
                .append(" payee_acct_bank_name, is_deleted,show_name)");
        sql.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramsList = new ArrayList<>(payeeAcctsList.size());
        for (PayeeAcct payeeAcct : payeeAcctsList) {
            Object[] params = new Object[]{
                    payeeAcct.getId(), payeeAcct.getMofDivCode(), payeeAcct.getYear(), payeeAcct.getCreateTime(),
                    payeeAcct.getUpdateTime(), payeeAcct.getPayeeAcctName(), payeeAcct.getPayeeAcctCode(), payeeAcct.getPayeeAcctNo(),
                    payeeAcct.getPayeeAcctBankName(), payeeAcct.getIsDeleted(),payeeAcct.getShowName()
            };
            paramsList.add(params);
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 11; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
